with tab as (
 select billcode,
        scansitecode,
        split(concat(scantime,'\001',
        coalesce(inputtime,''),'\001',
        coalesce(destination,''),'\001',
        coalesce(destsite,''),'\001',
        coalesce(expresstype,''),'\001',
        coalesce(nextstationcode,''),'\001',
        coalesce(operatedate,''),'\001',
        coalesce(packagecode,''),'\001',
        coalesce(scanuser,''),'\001',
        coalesce(source,''),'\001',
        coalesce(subbillcode,''),'\001',
        coalesce(scansitecode,'')
        ),'\001') as wid_columns
from jms_ods.tab_barscan_bagging      --21天内有效
where  dt BETWEEN date_add('{{ execution_date | cst_ds }}', -20) AND '{{ execution_date | cst_ds }}'
)

insert overwrite table jms_dwd.dwd_barscan_bagging_dt

select
billcode                                                 as billcode,
max(wid_columns)[11]                                     as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as destination_later,
max(wid_columns)[3]                                      as destsite_later,
max(wid_columns)[4]                                      as expresstype_later,
max(wid_columns)[5]                                      as nextstationcode_later,
max(wid_columns)[6]                                      as operatedate_later,
max(wid_columns)[7]                                      as packagecode_later,
max(wid_columns)[8]                                      as scanuser_later,
max(wid_columns)[9]                                      as source_later,
max(wid_columns)[10]                                     as subbillcode_later,

min(wid_columns)[11]                                     as scansitecode_early,
min(wid_columns)[0]                                      as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as destination_early,
min(wid_columns)[3]                                      as destsite_early,
min(wid_columns)[4]                                      as expresstype_early,
min(wid_columns)[5]                                      as nextstationcode_early,
min(wid_columns)[6]                                      as operatedate_early,
min(wid_columns)[7]                                      as packagecode_early,
min(wid_columns)[8]                                      as scanuser_early,
min(wid_columns)[9]                                      as source_early,
min(wid_columns)[10]                                     as subbillcode_early,
'billcode'                                               as dimtype,
date(max(wid_columns)[0])                                as dt

from tab
 group by billcode
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14)  AND '{{ execution_date | cst_ds }}'

union all

select
billcode                                                 as billcode,
scansitecode                                             as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as destination_later,
max(wid_columns)[3]                                      as destsite_later,
max(wid_columns)[4]                                      as expresstype_later,
max(wid_columns)[5]                                      as nextstationcode_later,
max(wid_columns)[6]                                      as operatedate_later,
max(wid_columns)[7]                                      as packagecode_later,
max(wid_columns)[8]                                      as scanuser_later,
max(wid_columns)[9]                                      as source_later,
max(wid_columns)[10]                                     as subbillcode_later,

scansitecode                                             as scansitecode_early,
min(wid_columns)[0]                                      as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as destination_early,
min(wid_columns)[3]                                      as destsite_early,
min(wid_columns)[4]                                      as expresstype_early,
min(wid_columns)[5]                                      as nextstationcode_early,
min(wid_columns)[6]                                      as operatedate_early,
min(wid_columns)[7]                                      as packagecode_early,
min(wid_columns)[8]                                      as scanuser_early,
min(wid_columns)[9]                                      as source_early,
min(wid_columns)[10]                                     as subbillcode_early,
'bill_scansite'                                          as dimtype,
date(max(wid_columns)[0])                                as dt

from tab
 group by billcode, scansitecode
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14)  AND '{{ execution_date | cst_ds }}'
    distribute by dimtype, dt, PMOD(HASH(RAND()), 50);

